#!/usr/bin/env bash
# db-create_calibration_view -- Creates a variable's *_calibration view in a MySQL database
# > eval "$(db-parse "$url")"
# > export DEEPDIVE_CALIBRATION_NUM_BUCKETS=10
# > db-create_calibration_view TABLE
##
set -euo pipefail

table=${1:?The TABLE holding the variable must be given, e.g., has_spouse}

: ${DEEPDIVE_CALIBRATION_NUM_BUCKETS:=10}
num_buckets=$DEEPDIVE_CALIBRATION_NUM_BUCKETS

db-create-view-as "dd_bucketed_${table}_inference" "
    SELECT dd_label AS label
         , CASE
             WHEN expectation = 1 THEN $(($num_buckets - 1))
             ELSE FLOOR(expectation * ${num_buckets})
           END AS bucket
    FROM ${table}_inference
"

db-create-view-as "dd_bucketed_${table}_inference_all" "
    SELECT bucket, COUNT(*) AS count
      FROM dd_bucketed_${table}_inference
     GROUP BY bucket
"

db-create-view-as "dd_bucketed_${table}_inference_positive" "
    SELECT bucket, COUNT(*) AS count
      FROM dd_bucketed_${table}_inference
     WHERE label = true
     GROUP BY bucket
"

db-create-view-as "dd_bucketed_${table}_inference_negative" "
    SELECT bucket, COUNT(*) AS count
      FROM dd_bucketed_${table}_inference
     WHERE label = false
     GROUP BY bucket
"

db-create-view-as "${table}_calibration" "
    SELECT universe.bucket                             AS bucket
         , universe.count                              AS num_variables
         , COALESCE(positive.count, 0)                 AS num_correct
         , COALESCE(negative.count, 0)                 AS num_incorrect
         , (universe.bucket     ) / $num_buckets.      AS probability_lo
         , (universe.bucket +  1) / ${num_buckets}.    AS probability_hi
         , (universe.bucket + .5) / ${num_buckets}.    AS probability
         , 1.0 * COALESCE(positive.count / (
               COALESCE(positive.count, 0) +
               COALESCE(negative.count, 0) )           AS accuracy
         , COALESCE(positive.count, 0) +
           COALESCE(negative.count, 0)                 AS num_predictions_test
         , universe.count                              AS num_predictions_whole
      FROM dd_bucketed_${table}_inference_all universe
      LEFT JOIN dd_bucketed_${table}_inference_positive positive ON universe.bucket = positive.bucket
      LEFT JOIN dd_bucketed_${table}_inference_negative negative ON universe.bucket = negative.bucket
     ORDER BY universe.bucket
"
